﻿unit MoveCollector;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, cxGraphics, cxControls, cxLookAndFeels, cxLookAndFeelPainters,
  cxGridExportLink,DateUtils,
  FIBQuery, pFIBCacheQueries,
  dxRibbonForm,
  dxSkinsCore, dxSkinMcSkin, dxSkinOffice2010Blue, dxSkinOffice2010Silver,
  dxSkinSeven, dxSkinscxPCPainter, Menus, dxLayoutControl, cxStyles,
  cxCustomData, cxFilter, cxData, cxDataStorage, cxEdit, DB, cxDBData,
  dxSkinsdxRibbonPainter, cxPropertiesStore, cxClasses, dxRibbon, cxGridLevel,
  cxGridCustomTableView, cxGridTableView, cxGridDBTableView, cxGridCustomView,
  cxGrid, cxSplitter, StdCtrls, cxButtons, dxLayoutLookAndFeels, FIBDatabase,
  pFIBDatabase, cxContainer, cxCheckListBox, MemTableDataEh, MemTableEh,
  FIBDataSet, pFIBDataSet, dxSkinOffice2010Black, cxCheckBox, dxDockPanel,
  dxDockControl, dxLayoutcxEditAdapters, cxTextEdit, cxMaskEdit, cxDropDownEdit,
  cxLookupEdit, cxDBLookupEdit, cxDBLookupComboBox, pFIBScripter, cxProgressBar,
  dxStatusBar, dxRibbonStatusBar;

type
  TMoveCollectorForm = class(Tdxcustomribbonform)
    lcntrlcontr122: TdxLayoutControl;
    btn1: TcxButton;
    lgrp1: TdxLayoutGroup;
    lgrpGrp: TdxLayoutGroup;
    lItem1: TdxLayoutItem;
    cxspltr1: TcxSplitter;
    lgrp_coll: TdxLayoutGroup;
    rtRibbon1Tab1: TdxRibbonTab;
    dxRibbon1: TdxRibbon;
    prop1: TcxPropertiesStore;
    LayoutLookAndFeelList1: TdxLayoutLookAndFeelList;
    LayoutCxLookAndFeel1: TdxLayoutCxLookAndFeel;
    lst1: TcxCheckListBox;
    lItemlcntrlcontr122Item1: TdxLayoutItem;
    trn1: TpFIBTransaction;
    lst2: TcxCheckListBox;
    lItemlcntrlcontr122Item11: TdxLayoutItem;
    table1: TMemTableEh;
    DS: TpFIBDataSet;
    t_union: TMemTableEh;
    ds1: TDataSource;
    lgrp: TdxLayoutGroup;
    dxLayoutControl1: TdxLayoutControl;
    lItemLayoutControl1Item1: TdxLayoutItem;
    grd1: TcxGrid;
    v1: TcxGridDBTableView;
    clAccCode: TcxGridDBColumn;
    clREESTR: TcxGridDBColumn;
    clYM: TcxGridDBColumn;
    clSUMMA: TcxGridDBColumn;
    clDSUMMA: TcxGridDBColumn;
    v2: TcxGridDBTableView;
    clChk: TcxGridDBColumn;
    clAcCcode1: TcxGridDBColumn;
    clusr1: TcxGridDBColumn;
    clName1: TcxGridDBColumn;
    cllasttime1: TcxGridDBColumn;
    clkontr1: TcxGridDBColumn;
    clAcCcode2: TcxGridDBColumn;
    clusr2: TcxGridDBColumn;
    clName2: TcxGridDBColumn;
    l1: TcxGridLevel;
    pm1: TPopupMenu;
    N1: TMenuItem;
    N2: TMenuItem;
    N3: TMenuItem;
    cbb1: TcxLookupComboBox;
    lItemLayoutControl1Item11: TdxLayoutItem;
    lgrpLayoutControl1Group1: TdxLayoutGroup;
    btn_change: TcxButton;
    lItemLayoutControl1Item12: TdxLayoutItem;
    dxLayoutControl1Group1: TdxLayoutGroup;
    DS_Coll: TpFIBDataSet;
    ds2: TDataSource;
    btn2: TcxButton;
    lItemlcntrlcontr122Item12: TdxLayoutItem;
    btn3: TcxButton;
    lItemlcntrlcontr122Item13: TdxLayoutItem;
    lgrplcntrlcontr122Group1: TdxLayoutGroup;
    lgrplcntrlcontr122Group2: TdxLayoutGroup;
    btn4: TcxButton;
    lItemlcntrlcontr122Item14: TdxLayoutItem;
    lgrplcntrlcontr122Group3: TdxLayoutGroup;
    btn5: TcxButton;
    lItemlcntrlcontr122Item15: TdxLayoutItem;
    dxlytspltrtmlcntrlcontr122SplitterItem1: TdxLayoutSplitterItem;
    dxlytspltrtmlcntrlcontr122SplitterItem2: TdxLayoutSplitterItem;
    script1: TpFIBScripter;
    stsbr1: TdxRibbonStatusBar;
    stsbr1Container1: TdxStatusBarContainerControl;
    prb1: TcxProgressBar;
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure btn1Click(Sender: TObject);
    procedure N1Click(Sender: TObject);
    procedure N2Click(Sender: TObject);
    procedure pm1Popup(Sender: TObject);
    procedure btn2Click(Sender: TObject);
    procedure btn3Click(Sender: TObject);
    procedure btn4Click(Sender: TObject);
    procedure btn5Click(Sender: TObject);
    procedure v2CellDblClick(Sender: TcxCustomGridTableView;
      ACellViewInfo: TcxGridTableDataCellViewInfo; AButton: TMouseButton;
      AShift: TShiftState; var AHandled: Boolean);
    procedure v2KeyUp(Sender: TObject; var Key: Word; Shift: TShiftState);
    procedure btn_changeClick(Sender: TObject);
  private
    { Private declarations }
    function GetInClause(ls :TcxCheckListBox; Quote :Boolean) :string;
    procedure Fillkontr(ls :TcxCheckListBox);
    procedure FillColl(ls :TcxCheckListBox);
    procedure MakeData;
    procedure CreateTable;
    procedure AddData;
    procedure DisableControll(disable :boolean);
    procedure ChangeCollector(col :integer);
  public
    { Public declarations }
  end;

var
  MoveCollectorForm: TMoveCollectorForm;

implementation

uses cmUtils, DModule;

{$R *.dfm}

procedure TMoveCollectorForm.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  RemoveBtn(Name);
  Action:=caFree;
end;

procedure TMoveCollectorForm.FormCreate(Sender: TObject);
begin
 LoadForm(prop1,Self.Name);
 AddButton(self);
 trn1.StartTransaction;
 Fillkontr(lst1);
 FillColl(lst2);
 CreateTable;
 DS_Coll.Open;
end;
{------------------------------------------------------------------------------
Fillkontr:  Заполняем список контрагентов
_______________________________________________________________________________}
procedure TMoveCollectorForm.Fillkontr(ls :TcxCheckListBox);
var
  Q :TFIBQuery;
begin
  Q:=GetQueryForUse(trn1,'Select code,name from SPR$KONTRAGENT');
  Q.ExecQuery;
  ls.Clear;
  while not Q.Eof do
  begin
    with ls.Items.Add do
    begin
      try
        State:=cbsUnchecked;
        Text:=Q.FN('code').AsString+#9+' '+Q.FN('name').AsString;
      except
        on E:Exception do
        ShowMessage(Q.FN('code').AsString+#9+' '+Q.FN('name').AsString);
      end;
    end;
    //ls.Add(Q.FN('code').AsString+#9+Q.FN('name').AsString);
    Q.Next;
  end;
  FreeQueryForUse(Q);
end;
{------------------------------------------------------------------------------
FillColl:  Заполняем список коллеkторов
_______________________________________________________________________________}
procedure TMoveCollectorForm.FillColl(ls :TcxCheckListBox);
var
  Q :TFIBQuery;
begin
  Q:=GetQueryForUse(trn1,'select code,name from spr$users order by name');
  Q.ExecQuery;
  ls.Clear;
  while not Q.Eof do
  begin
    with ls.Items.Add do
    begin
      State:=cbsUnchecked;
      Text:=Q.FN('code').AsString+#9+' '+Q.FN('name').AsString;
    end;
    Q.Next;
  end;
  FreeQueryForUse(Q);
end;
{------------------------------------------------------------------------------
GetInClause:  Формируем условие IN
_______________________________________________________________________________}
function TMoveCollectorForm.GetInClause(ls :TcxCheckListBox; Quote :Boolean) :string;
var
  i :Integer;
  s :string;
begin
  s:='';
  for i := 0 to ls.Count - 1 do
  begin
    if ls.Items[i].Checked then
    begin
      if Quote=true then
      begin
        if s<>'' then
        s:=s+','+#39+GetWord(ls.Items[i].Text,1,#9)+#39 else
        s:=s+#39+GetWord(ls.Items[i].Text,1,#9)+#39;
      end else
      begin
        if s<>'' then
        s:=s+','+GetWord(ls.Items[i].Text,1,#9) else
        s:=s+GetWord(ls.Items[i].Text,1,#9);
      end;
    end;
  end;
  Result:=s;
end;
{------------------------------------------------------------------------------
  btn1Click:  Cформировать
_______________________________________________________________________________}
procedure TMoveCollectorForm.btn1Click(Sender: TObject);
begin
  MakeData;
end;

procedure TMoveCollectorForm.btn2Click(Sender: TObject);
var
  i :Integer;
begin
   for i:=0 to lst1.Count-1 do
   begin
     lst1.Items[i].Checked:=true;
   end;
end;

procedure TMoveCollectorForm.btn3Click(Sender: TObject);
var
  i :Integer;
begin
   for i:=0 to lst2.Count-1 do
   begin
     lst2.Items[i].Checked:=true;
   end;
end;

procedure TMoveCollectorForm.btn4Click(Sender: TObject);
var
  i :Integer;
begin
   for i:=0 to lst1.Count-1 do
   begin
     lst1.Items[i].Checked:=false;
   end;
end;

procedure TMoveCollectorForm.btn5Click(Sender: TObject);
var
  i :Integer;
begin
   for i:=0 to lst2.Count-1 do
   begin
     lst2.Items[i].Checked:=false;
   end;
end;
{------------------------------------------------------------------------------
TMoveCollectorForm.btn_changeClick:  Сменить
_______________________________________________________________________________}
procedure TMoveCollectorForm.btn_changeClick(Sender: TObject);
begin
 if cbb1.ItemIndex>-1 then
 ChangeCollector(cbb1.Properties.Grid.DataController.Values
                      [cbb1.Properties.Grid.FocusedRowIndex,0]);

end;
{------------------------------------------------------------------------------
ChangeCollector:  Меняем отмечееные записи на выбранного коллектора
_______________________________________________________________________________}
procedure TMoveCollectorForm.ChangeCollector(col :integer);
var
  ls :TStringList;
begin
  ls:=TStringList.Create;
  try
    DisableControll(true);
    t_union.First;
    while not t_union.Eof do
    begin
     if t_union.FieldByName('Chk').AsInteger=1 then
     ls.Add('update N_REESTRDATA set USR ='+IntToSTr(col)+
            ' where ACCODE='#39+t_union.FieldByName('ACCCODE').AsString+#39+';');
     t_union.Next;
    end;
  finally
     DisableControll(false);
  end;
  ls.SaveToFile(ExtractFilePath(Application.ExeName)+'ChangeColl.sql');
  if trn1.Active=true then trn1.Active:=False;
  script1.Script:=ls;
  try
    if trn1.Active=false then
       trn1.StartTransaction;
    script1.ExecuteScript();
  finally
    trn1.Commit;
  end;
  ls.Free;
end;

procedure TMoveCollectorForm.DisableControll(disable :boolean);
begin
 if disable=true then
 begin
  lcntrlcontr122.Enabled:=False;
  dxLayoutControl1.Enabled:=False;
  v2.DataController.DataSource:=nil;
 end else
 begin
  lcntrlcontr122.Enabled:=true;
  dxLayoutControl1.Enabled:=true;
  v2.DataController.DataSource:=ds1;
 end;
end;

{------------------------------------------------------------------------------
MakeData:  Формируем отчет
_______________________________________________________________________________}
procedure TMoveCollectorForm.MakeData;
var
  kontr, coll :string;
begin
 DS.Close;
 kontr:=GetInClause(lst1,True);
 coll:=GetInClause(lst2,False);
 DS.SelectSQL.Clear;
 DS.SelectSQL.Text:=
   'select r.acccode,r.usr,su.name U_Name,r.kontragent,sk.name k_name,'+
   ' ss.addr,sa.name A_Name,ss.master'+
   ' from reestr r'+
   ' left outer join spr$schet ss on r.acccode=ss.code'+
   ' left outer join spr$kontragent sk on r.kontragent=sk.code'+
   ' left outer join spr$addr sa on ss.addr=sa.code'+
   ' left outer join spr$users su on r.usr=su.code'+
   ' where r.state=1';
   if kontr<>'' then
   DS.SelectSQL.Add(' and r.kontragent in ('+kontr+')');
   if coll<>'' then
   DS.SelectSQL.Add(' and r.usr in ('+coll+')');
   DS.SelectSQL.Add(' order by ss.addr');
 DS.Open;
 try
   DisableControll(true);
   if not table1.IsEmpty  then  table1.EmptyTable;
   if not t_union.IsEmpty then  t_union.EmptyTable;
   AddData;
 finally
   DisableControll(false);
 end;

 v2.ApplyBestFit();
 {if not table1.IsEmpty then table1.EmptyTable;
 table1.LoadFromDataSet(DS,-1,lmCopy,True);}
end;
procedure TMoveCollectorForm.N1Click(Sender: TObject);
var
  i :Integer;
  ls :TcxCheckListBox;
begin
   if lst1.IsFocused then ls:=lst1 else
   if lst2.IsFocused then ls:=lst2;
   for i:=0 to ls.Count-1 do
   begin
     ls.Items[i].Checked:=True;
   end;
end;

procedure TMoveCollectorForm.N2Click(Sender: TObject);
var
  i :Integer;
  ls :TcxCheckListBox;
begin
   if lst1.IsFocused then ls:=lst1 else
   if lst2.IsFocused then ls:=lst2;
   for i:=0 to ls.Count-1 do
   begin
     ls.Items[i].Checked:=false;
   end;
end;

procedure TMoveCollectorForm.pm1Popup(Sender: TObject);
begin
 if (lst1.IsFocused=false) and (lst2.IsFocused=false) then
 Exit;
end;

procedure TMoveCollectorForm.v2CellDblClick(Sender: TcxCustomGridTableView;
  ACellViewInfo: TcxGridTableDataCellViewInfo; AButton: TMouseButton;
  AShift: TShiftState; var AHandled: Boolean);
begin
  t_union.Edit;
  if t_union.FieldByName('Chk').AsInteger=1 then
     t_union.FieldByName('Chk').AsInteger:=0 else
     t_union.FieldByName('Chk').AsInteger:=1;
  t_union.Post;
end;

procedure TMoveCollectorForm.v2KeyUp(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
 if Key=VK_SPACE then
 begin
   t_union.Edit;
   if t_union.FieldByName('Chk').AsInteger=1 then
     t_union.FieldByName('Chk').AsInteger:=0 else
     t_union.FieldByName('Chk').AsInteger:=1;
   t_union.Post;
 end;
end;

{-------------------------------------------------------------------------------
AddData | Добавляем записи во временную таблицу
-------------------------------------------------------------------------------}
procedure TMoveCollectorForm.AddData;
var
  Addr :string;
  coll :Integer;
  procedure InTAble;
  begin
    table1.Append;
    table1.FieldByName('ACCCODE').AsString:=DS.FN('ACCCODE').AsString;
    table1.FieldByName('USR').AsInteger:=DS.FN('USR').AsInteger;
    table1.FieldByName('U_NAME').AsString:=DS.FN('U_NAME').AsString;
    table1.FieldByName('KONTRAGENT').AsString:=DS.FN('KONTRAGENT').AsString;
    table1.FieldByName('K_NAME').AsString:=DS.FN('K_NAME').AsString;
    table1.FieldByName('ADDR').AsString:=DS.FN('ADDR').AsString;
    table1.FieldByName('A_NAME').AsString:=DS.FN('A_NAME').AsString;
    table1.FieldByName('MASTER').AsString:=DS.FN('MASTER').AsString;
    table1.Post;
  end;
  procedure InNewTable;
  begin
    table1.Last;
    t_union.Append;
    t_union.FieldByName('Chk').AsInteger:=0;
    t_union.FieldByName('ACCCODE').AsString:=table1.FieldByName('ACCCODE').AsString;
    t_union.FieldByName('USR').AsInteger:=table1.FieldByName('USR').AsInteger;
    t_union.FieldByName('U_NAME').AsString:=table1.FieldByName('U_NAME').AsString;
    t_union.FieldByName('KONTRAGENT').AsString:=table1.FieldByName('KONTRAGENT').AsString;
    t_union.FieldByName('K_NAME').AsString:=table1.FieldByName('K_NAME').AsString;
    t_union.FieldByName('ADDR').AsString:=table1.FieldByName('ADDR').AsString;
    t_union.FieldByName('A_NAME').AsString:=table1.FieldByName('A_NAME').AsString;
    t_union.FieldByName('MASTER').AsString:=table1.FieldByName('MASTER').AsString;
    t_union.Post;

    t_union.Append;
    t_union.FieldByName('Chk').AsInteger:=0;
    t_union.FieldByName('ACCCODE').AsString:=DS.FN('ACCCODE').AsString;
    t_union.FieldByName('USR').AsInteger:=DS.FN('USR').AsInteger;
    t_union.FieldByName('U_NAME').AsString:=DS.FN('U_NAME').AsString;
    t_union.FieldByName('KONTRAGENT').AsString:=DS.FN('KONTRAGENT').AsString;
    t_union.FieldByName('K_NAME').AsString:=DS.FN('K_NAME').AsString;
    t_union.FieldByName('ADDR').AsString:=DS.FN('ADDR').AsString;
    t_union.FieldByName('A_NAME').AsString:=DS.FN('A_NAME').AsString;
    t_union.FieldByName('MASTER').AsString:=DS.FN('MASTER').AsString;
    t_union.Post;
  end;
begin
 prb1.Properties.Min:=0;
 prb1.Properties.Max:=DS.RecordCountFromSrv;
 prb1.Position:=0;
 while not DS.eof do
 begin
   Addr:=DS.FN('ADDR').AsString;
   coll:=DS.FN('USR').AsInteger;
   if (table1.FieldByName('ADDR').AsString=Addr) and
   (table1.FieldByName('USR').AsInteger<>coll) then
   InNewTAble else
   InTAble;
   //DS.Prior
    prb1.Position:=DS.RecNo;
    Application.ProcessMessages;
   DS.Next;
 end;
  prb1.Position:=0;
end;
{------------------------------------------------------------------------------
CreateTable:  Создаем таблицу в памяти
_______________________________________________________________________________}
procedure TMoveCollectorForm.CreateTable;
begin
  with table1.FieldDefs do
  begin
    Add('ACCCODE',ftString,32);     //счет
    Add('USR',ftInteger);           //пользователь
    Add('U_NAME',ftString,128);     //имя пользователя
    Add('KONTRAGENT',ftString,32);  //контрагент
    Add('K_NAME',ftString,256);     //имя контрагента
    Add('ADDR',ftString,32);        //адресс
    Add('A_NAME',ftString,256);     //имя адреса
    Add('MASTER',ftString,256);     //владелец
  end;
  table1.CreateDataSet;
  table1.open;
  with t_union.FieldDefs do
  begin
    Add('Chk',ftInteger);           //пользователь
    Add('ACCCODE',ftString,32);     //счет
    Add('USR',ftInteger);           //пользователь
    Add('U_NAME',ftString,128);     //имя пользователя
    Add('KONTRAGENT',ftString,32);  //контрагент
    Add('K_NAME',ftString,256);     //имя контрагента
    Add('ADDR',ftString,32);        //адресс
    Add('A_NAME',ftString,256);     //имя адреса
    Add('MASTER',ftString,256);     //владелец
  end;
  t_union.CreateDataSet;
  t_union.open;
end;

end.
